var express = require('express');
var router = express.Router();

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

/**
 * 开始采集微信小程序商店的数据
 */
router.get('/data', function (req, res, next) {

	var request = require('request');
	var cheerio = require('cheerio');
	var mysql      = require('mysql');
	var async = require('async');
	var dbConfig = require('../config/db');

	var pool = mysql.createPool(dbConfig.mysql);

	let tagUrl = "https://minapp.com/miniapp/";

	//获取全部小程序的标签
	request({
		url : tagUrl,
		method : 'GET'
	},function(err, res, body) {
		//请求到body
		if(err){
			console.error('[ERROR]Collection' + err);
		}

		$ = cheerio.load(body);

		//获取小程序的全部分类/标签
		var allCateObj = $('.js-header-category-ul li');
		var allCateArray = [];
		for (let i = 1; i < allCateObj.length; i++ ){
			let cateData = [];
			let cateObj = allCateObj.eq(i);
			cateData.push(cateObj.attr('data-category'));
			cateData.push(cateObj.text());
			cateData.push(parseInt(Date.now()/1000));
			cateData.push(i);
			allCateArray.push(cateData);
		}

		// pool.getConnection(function (err, connection) {
		//
		// 	connection.query("INSERT INTO tags (id, name, created_at, sort_order) VALUES ? ", [allCateArray], function (err, result) {
		// 		if(err){
		// 			console.log(err);
		// 		}
		// 		console.log(result);
		// 		console.log('-----------------------------');
		// 		connection.release();
		// 	});
		// });

	});

	//获取全部小程序
	var listUrl = "https://minapp.com/api/v3/trochili/miniapp/?limit=1000";
	request({
		url : listUrl,
		method : 'GET'
	},function(err, res, body) {
		//请求到body
		if(err){
			console.error('[ERROR]Collection' + err);
		}
		let listObj = JSON.parse(body);

		//解释数据
		var appsObjects = listObj.objects;

		//循环插入数据库
		async.eachSeries(appsObjects, function(item, callback) {

			pool.getConnection(function (err, connection) {

				connection.query("INSERT INTO apps (id, name, description, created_at, qrcode, icon) VALUES (?, ?, ?, ?, ?, ?) ", [item.id, item.name, item.description, item.created_at, item.qrcode.image, item.icon.image], function (err, result) {
					if(err){
						callback(err);
					}

					//插入屏幕截图
					item.screenshot.forEach(function (v) {
						connection.query("INSERT INTO screenshots (id, app_id, image, created_at) VALUES (?, ?, ?, ?)", [v.id, item.id, v.image, item.created_at], function (err, result) {

						});
					});

					//插入标签
					item.tag.forEach(function (v) {
						connection.query("INSERT INTO apps_tags (app_id, tag_id) VALUES (?, ?)", [item.id, v.id], function (err, result) {

						});
					});

					connection.release();
					callback();
				});

			});

		}, function(err) {
			// 所有SQL执行完成后回调
			if(err) {
				console.log(err);
			} else {
				console.log("SQL全部执行成功");
			}
		});

		//获取app的评论
		async.eachSeries(appsObjects, function(item, callback) {

			pool.getConnection(function (err, connection) {
					//https://minapp.com/api/v3/trochili/miniapp/5/rate/?offset=0&limit=1000
				let CommentUrl = "https://minapp.com/api/v3/trochili/miniapp/" + item.id.toString() + "/rate/?offset=0&limit=1000";
				request({
					url : CommentUrl,
					method : 'GET'
				},function(err, res, body) {
					//请求到body
					if (err) {
						console.error('[ERROR]Collection' + err);
					}
					let commentObj = JSON.parse(body);
					commentObj = commentObj.objects;

					//插入用户
					commentObj.forEach(function (v) {

						//插入用户
						let userObj = v.created_by;
						//插入评论
						connection.query("INSERT INTO comments (id, app_id, user_id, content, score, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)", [v.id, item.id, userObj.id, v.content, v.score, parseInt(new Date(v.created_at).getTime()/1000), v.updated_at], function (err, result) {
							if (err){
								console.error('[ERROR] Comments' + err);
							}else{

							}
						});
						connection.query("INSERT INTO users (id, name, nickname, avatar_url) VALUES (?, ?, ?, ?)", [userObj.id, userObj.nickname.toString(), userObj.nickname.toString(), userObj.avatar_url], function (err, result) {
							if (err){
								console.error(' [ERROR] Uusers ' + err);
								console.log(userObj);
							}else{

							}
						});

					});

					callback();
				});

			});

		}, function(err) {
			// 所有SQL执行完成后回调
			if(err) {
				console.log(err);
			} else {
				console.log("SQL全部执行成功");
			}
		});

	});

  res.render('index', { title: 'data' });
});

module.exports = router;
